﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sicurezza.CA.Entita;
using System.Data.OleDb;

namespace Sicurezza.CA.DataProvider
{
    public class CertificateDataProvider
    {
        public List<Certificate> GetAll()
        {
            List<Certificate> certs = new List<Certificate>();
            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "SELECT * " +
                               "FROM ((Certificates c LEFT JOIN Revokes r ON c.id = r.serial) INNER JOIN Profiles p on c.ProfileId = p.Id) " +
                               "WHERE r.id IS NULL " +
                               "AND #" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "# BETWEEN notBefore AND notAfter ";

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open();
                OleDbDataReader dr = cmd.ExecuteReader();

                if(dr.HasRows)
                {
                    certs = BuildList(dr);
                }
            }

            return certs;
        }

        private List<Certificate> BuildList(OleDbDataReader dr)
        {
            List<Certificate> list = new List<Certificate>();

            while (dr.Read())
            { 
                Certificate c = new Certificate();
                c.Serial= Convert.ToInt32(dr["c.id"]);
                c.NotBefore = Convert.ToDateTime(dr["notbefore"]);
                c.NotAfter = Convert.ToDateTime(dr["notafter"]);
                c.DN = "C=" + Convert.ToString(dr["C"]).Trim() + ", ";
                c.DN += "O=" + Convert.ToString(dr["O"]).Trim() + ", ";
                c.DN += "OU=" + Convert.ToString(dr["OU"]).Trim() + ", ";
                c.DN += "CN=" + Convert.ToString(dr["CN"]).Trim();
                c.EMail =Convert.ToString(dr["Email"]).Trim();
                c.DigitalSignature = Convert.ToBoolean(dr["digitalsignature"]);
                c.KeyEncipherment = Convert.ToBoolean(dr["KeyEncipherment"]);

                list.Add(c);

            }

            return list;
        }
    }
}
